package hbec.kettle.etl.plugins.core;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.pentaho.di.compatibility.Value;
import org.pentaho.di.core.Const;
import org.pentaho.di.core.database.DatabaseMeta;
import org.pentaho.di.core.exception.KettleException;
import org.pentaho.di.trans.Trans;
import org.pentaho.di.trans.TransHopMeta;
import org.pentaho.di.trans.TransMeta;
import org.pentaho.di.trans.step.StepMeta;
import org.pentaho.di.trans.step.StepMetaInterface;
import org.pentaho.di.trans.steps.dummytrans.DummyTransMeta;
import org.pentaho.di.trans.steps.sql.ExecSQLMeta;
import org.pentaho.di.trans.steps.switchcase.SwitchCaseMeta;
import org.pentaho.di.trans.steps.switchcase.SwitchCaseTarget;
import org.pentaho.di.trans.steps.tableinput.TableInputMeta;

import hbec.kettle.etl.common.util.DbcpUtils;

public final class TransBuilder {
	
	private static Map<String, DatabaseMeta> databaseMetaWrapper = new HashMap<>();
	
	static {
		for (Map.Entry<String, Map<String, String>> e : DbcpUtils.dbUrls.entrySet()) {
			DatabaseMeta databaseMeta = new DatabaseMeta(
						e.getKey(),
						e.getValue().get("TYPE"),
						"Native",
						e.getValue().get("IP"),
						e.getValue().get("DATABASENAME"),
						e.getValue().get("PORT"),
						e.getValue().get("USER"),
						e.getValue().get("PASSWORD")
					);
			databaseMeta.setUsingConnectionPool(true);
			databaseMeta.setInitialPoolSize(4);
			databaseMeta.setMaximumPoolSize(8);
			databaseMeta.setShared(true);
			Properties poolingProperties = new Properties();
			poolingProperties.put("defaultAutoCommit", true);
			poolingProperties.put("initialSize", 4);
			poolingProperties.put("maxActive", 20);
			poolingProperties.put("maxIdle", 8);
			poolingProperties.put("minIdle", 4);
			poolingProperties.put("maxWait", 10000);
			poolingProperties.put("validationQuery", "SELECT 1;");
			poolingProperties.put("testOnBorrow", false);
			poolingProperties.put("testOnReturn", false);
			poolingProperties.put("testWhileIdle", true);
			poolingProperties.put("timeBetweenEvictionRunsMillis", 30000);
			poolingProperties.put("removeAbandoned", false);
			poolingProperties.put("removeAbandonedTimeout", 300);
			poolingProperties.put("logAbandoned", false);
			databaseMeta.setConnectionPoolingProperties(poolingProperties);
			databaseMetaWrapper.put(e.getKey(), databaseMeta);
		}
	}

	public static TransMeta getSyncTableTransMeta(String transformationName,
			String sourceDatabaseName, String sourceTableName,
			String[] sourceFields, String targetDatabaseName,
			String targetTableName, String[] targetFields) throws KettleException {
		
		try {
			// Create a new transformation meta
			TransMeta transMeta = new TransMeta();
			transMeta.setName(transformationName);
			
			// Add the database connections
			// transMeta.addDatabase(databaseMeta);
			// DatabaseMeta sourceDBInfo = transMeta.findDatabase(sourceDatabaseName);
			// DatabaseMeta targetDBInfo  = transMeta.findDatabase(targetDatabaseName);
			transMeta.addDatabase(databaseMetaWrapper.get(sourceDatabaseName));
			transMeta.addDatabase(databaseMetaWrapper.get(targetDatabaseName));
			DatabaseMeta sourceDBInfo = transMeta.findDatabase(sourceDatabaseName);
			DatabaseMeta targetDBInfo  = transMeta.findDatabase(targetDatabaseName);
			
			TableInputMeta watchMeta = new TableInputMeta();
			watchMeta.setDatabaseMeta(sourceDBInfo);
			watchMeta.setVariableReplacementActive(true);
			StringBuilder watchSql = new StringBuilder("");
			watchSql.append("SELECT identity, action " + Const.CR);
			watchSql.append("FROM ${WATCH_SUMTABLE} " + Const.CR);
			watchSql.append("WHERE id BETWEEN ${MIN_POS} AND ${MAX_POS} AND object = '${WATCH_TABLE}' " + Const.CR);
			watchSql.append("ORDER BY id ASC");
			watchMeta.setSQL(watchSql.toString());
			StepMeta watchStep = new StepMeta("watchStep", (StepMetaInterface) watchMeta);
			watchStep.setLocation(50, 300);
			watchStep.setDraw(true);
			watchStep.setDescription("watchStep");
			transMeta.addStep(watchStep);
			
			TableInputMeta fromMeta = new TableInputMeta();
			fromMeta.setDatabaseMeta(sourceDBInfo);
			fromMeta.setVariableReplacementActive(true);
			fromMeta.setExecuteEachInputRow(true);
			StringBuilder fromSql = new StringBuilder("");
			fromSql.append("SELECT " + Const.CR);
			for (int i = 0; i < sourceFields.length; i++) {
				if (i > 0) fromSql.append(", "); else fromSql.append(" ");
				fromSql.append(sourceFields[i] + " as " + targetFields[i] + Const.CR);
			}
			fromSql.append("FROM " + sourceTableName + " " + Const.CR);
			fromSql.append("WHERE object_id = ? and ? in ('INSERT','UPDATE') ");
			fromMeta.setSQL(fromSql.toString());
			StepMeta fromStep = new StepMeta("fromStep", (StepMetaInterface) fromMeta);
			fromStep.setLocation(250, 200);
			fromStep.setDraw(true);
			fromStep.setDescription("fromStep");
			transMeta.addStep(fromStep);
			
			ExecSQLMeta toMeta = new ExecSQLMeta();
			toMeta.setDatabaseMeta(targetDBInfo);
			toMeta.setVariableReplacementActive(true);
			toMeta.setExecutedEachInputRow(true);
			toMeta.setSingleStatement(true);
			toMeta.setParams(true);
			StringBuilder toSql = new StringBuilder("");
			toSql.append("INSERT INTO " + targetTableName + " " + Const.CR);
			String placeholder = "";
			String setFields = "";
			toSql.append("(");	
			for (int i = 0; i < targetFields.length; i++) {
				if (i > 0) toSql.append(", "); else fromSql.append("");
				if (i > 0) placeholder += ", "; else placeholder += "";
				toSql.append(targetFields[i]);
				placeholder += "?";
				if (targetFields[i] != "object_id") {
					if (i > 0 && !"".equals(setFields)) setFields += ", "; else setFields += "";
					setFields += targetFields[i] + "=VALUES(" + targetFields[i] + ")";
				}
			}
			toSql.append(") " + Const.CR);
			toSql.append("VALUES (" + placeholder + ") " + Const.CR);
			if (!"".equals(setFields)) {
				toSql.append("ON DUPLICATE KEY UPDATE " + Const.CR);
				toSql.append(setFields);
			}
			toMeta.setSql(toSql.toString());
			toMeta.setArguments(targetFields);
			StepMeta toStep = new StepMeta("toStep", (StepMetaInterface) toMeta);
			toStep.setLocation(450, 200);
			toStep.setDraw(true);
			toStep.setDescription("toStep");
			transMeta.addStep(toStep);
			
			DummyTransMeta dummyMeta = new DummyTransMeta();
			StepMeta dummyStep = new StepMeta("dummyStep", (StepMetaInterface) dummyMeta);
			dummyStep.setLocation(250, 400);
			dummyStep.setDraw(true);
			dummyStep.setDescription("dummyStep");
			transMeta.addStep(dummyStep);
			
			SwitchCaseMeta sc = new SwitchCaseMeta();
			sc.setFieldname("action");
			sc.setCaseValueType(Value.VALUE_TYPE_STRING);
			List<SwitchCaseTarget> caseTargets = new ArrayList<>();
			SwitchCaseTarget sct = new SwitchCaseTarget();
			sct.caseValue = "DELETE";
			sct.caseTargetStep = dummyStep;
			caseTargets.add(sct);
			sc.setCaseTargets(caseTargets);
			sc.setDefaultTargetStep(fromStep);
			StepMeta switchStep = new StepMeta("switchStep", (StepMetaInterface) sc);
			switchStep.setLocation(150, 300);
			switchStep.setDraw(true);
			switchStep.setDescription("switchStep");
			transMeta.addStep(switchStep);
			
			fromMeta.setLookupFromStep(switchStep);
			
			TransHopMeta h1 = new TransHopMeta(watchStep, switchStep);
			transMeta.addTransHop(h1);
			TransHopMeta h2 = new TransHopMeta(switchStep, fromStep);
			transMeta.addTransHop(h2);
			TransHopMeta h3 = new TransHopMeta(switchStep, dummyStep);
			transMeta.addTransHop(h3);
			TransHopMeta h4 = new TransHopMeta(fromStep, toStep);
			transMeta.addTransHop(h4);
			
			return transMeta;
		} catch (Exception e) {
			throw new KettleException("An unexpected error occurred creating the new transformation meta", e);
		} 
	}

	public static Trans getSyncTableTrans() {
		return null;
	}

	public static TransMeta getInternalTableTransMeta() {
		return null;
	}

	public static Trans getInternalTableTrans() {
		return null;
	}

	public static TransMeta getQualityTableTransMeta() {
		return null;
	}

	public static Trans getQualityTableTrans() {
		return null;
	}
}
